﻿<%@ Page Title="" Language="C#" MasterPageFile="~/Admin/Master/ConfigAdmin.Master" AutoEventWireup="true"
    CodeBehind="LabelSet.aspx.cs" Inherits="Besture.UI.Admin.LabelManage.LabelSet" %>

<asp:Content ID="Content1" ContentPlaceHolderID="Head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="Content" runat="server">
    <div class="mainbox">
        <div class="setTabCont">
            <div>
                <table width="98%" cellspacing="0" cellpadding="0" class="table_form">
                    <tr>
                        <th style="width: 15%">
                            标签名称：
                        </th>
                        <td style="width: 35%">
                            <cms:TextBox runat="server" ID="Name" Required="True" MaxLength="200" WidthExt="Width_200"></cms:TextBox>
                        </td>
                        <th style="width: 15%">
                            查询数量：
                        </th>
                        <td style="width: 35%">
                            <cms:TextBox runat="server" ID="Count" MaxLength="10" WidthExt="Width_100" Text="10"
                                ValidateExt="Number"></cms:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <th style="width: 15%">
                            主表：
                        </th>
                        <td style="width: 35%">
                            <asp:DropDownList runat="server" ID="ddlTable1">
                            </asp:DropDownList>
                        </td>
                        <th style="width: 15%">
                            从表：
                        </th>
                        <td style="width: 35%">
                            <asp:DropDownList runat="server" ID="ddlTable2">
                            </asp:DropDownList>
                        </td>
                    </tr>
                    <tr>
                        <td colspan="2">
                            <div style="margin: 5px 0px; width: 100%; height: 200px; overflow: auto; float: left;">
                                <table width="96%" class="nx_table_table" cellspacing="0" cellpadding="0">
                                    <tbody id="TBZhu">
                                        <tr>
                                            <td style="width: 20%; text-align: center">
                                                字段名
                                            </td>
                                            <td style="width: 15%; text-align: center">
                                                类型
                                            </td>
                                            <td style="width: 15%; text-align: center">
                                                显示
                                            </td>
                                            <td style="width: 35%; text-align: center">
                                                条件
                                            </td>
                                            <td style="width: 15%; text-align: center">
                                                排序
                                            </td>
                                        </tr>
                                    </tbody>
                                </table>
                            </div>
                        </td>
                        <td colspan="2">
                            <div style="margin: 5px 0px; width: 100%; height: 200px; overflow: auto; float: left;">
                                <table width="96%" class="nx_table_table" cellspacing="0" cellpadding="0">
                                    <tbody id="TBCong">
                                        <tr>
                                            <td style="width: 20%; text-align: center">
                                                字段名
                                            </td>
                                            <td style="width: 15%; text-align: center">
                                                类型
                                            </td>
                                            <td style="width: 15%; text-align: center">
                                                显示
                                            </td>
                                            <td style="width: 35%; text-align: center">
                                                条件
                                            </td>
                                            <td style="width: 15%; text-align: center">
                                                排序
                                            </td>
                                        </tr>
                                    </tbody>
                                </table>
                            </div>
                        </td>
                    </tr>
                    <tr>
                        <th style="width: 15%">
                            联接字段：
                        </th>
                        <td style="width: 85%">
                            主表字段<select id="ZhuTableColumn">
                                        <option>请选择字段</option>
                                    </select>
                                    
                            从表字段<select id="CongTableColumn">
                                        <option>请选择字段</option>
                                    </select>
                        </td>
                    </tr>
                    <tr>
                        <th style="width: 15%">
                            执行：
                        </th>
                        <td style="width: 85%">
                            <input type="button" class="button" value="根据条件生成SQL" onclick="LoadColumn()" />
                            <input type="button" class="button" value="测试SQL语句" onclick="ShowDialog('测试SQL语句','/Admin/LabelManage/ExecuteSql.aspx?id=<%=txtSql.ClientID %>','1000','600')"  />
                        </td>
                    </tr>
                    <tr>
                        <th style="width: 15%">
                            SQL语句：
                        </th>
                        <td style="width: 85%" colspan="3">
                            <cms:TextBox runat="server" ID="txtSql" Required="False" TextMode="MultiLine" Width="800px"
                                Height="100px"></cms:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <th style="width: 15%">
                            备注：
                        </th>
                        <td style="width: 85%" colspan="3">
                            <cms:TextBox runat="server" ID="txtRemark" Required="False" TextMode="MultiLine" Width="800px"
                                Height="100px"></cms:TextBox>
                        </td>
                    </tr>
                </table>
            </div>
            <div style="display: none;">
                <cms:Seo ID="ControlSeo" runat="server" />
            </div>
        </div>
        <div class="buttonDiv">
            <asp:Button runat="server" ID="btnSubmit" CssClass="button" Text="保存" OnClick="btnSubmit_Click" />
            <input type="button" class="button" value="取消" onclick="location.href='LabelList.aspx'" />
        </div>
    </div>
    <script type="text/javascript">

        //主表名称
        var zhuTableName = $("#<%=ddlTable1.ClientID %>").val();

        var zhuTableCoulumnCounts = 0;
        //显示字段字组
        var zhuTableDisplayCoulumnArray = new Array();
        //显示主表字段集合
        var zhuTableDisplayColumn = "";
        //主表排序字符
        var orderBy = "";
        //主表排序字段
        var zhuTableOrderByArray = new Array();
        //主表条件字段
        var zhuTableConditionByArray = new Array();

        //显示从表字段集合
        var congTableDisplayColumn = "";
        //显示从表字段字组
        var congTableDisplayCoulumnArray = new Array();
        //从表名称
        var congTableName = $("#<%=ddlTable2.ClientID %>").val();
        //从表排序字符
        var congOrderBy = "";
        //从表排序字段
        var congTableOrderByArray = new Array();
        //从表条件字段
        var congTableConditionByArray = new Array();

        $(function () {
            $("#<%=ddlTable1.ClientID %>").change(function () {
                zhuTableName = $(this).val();
                //清空显示字段集合
                zhuTableDisplayColumn = "";
                //清空SQL
                $("#<%=txtSql.ClientID %>").val("");
                $.ajax({
                    type: "get", //使用get方法访问后台
                    dataType: "json", //返回json格式的数据
                    url: "/Admin/LabelManage/GetTableColumn.aspx", //要访问的后台地址
                    data: "table=" + $(this).val(), //要发送的数据
                    success: function (msg) { //msg为返回的数据，在这里做数据绑定
                        var data = msg.Table;
                        var html = "";
                        $(".autoappend").remove();
                        //联接字段-主表 恢复
                        $("#ZhuTableColumn").html("<option>请选择</option>");
                        $.each(data, function (i, n) {
                            //联接字段-主表 添加字段
                            $("#ZhuTableColumn").append("<option>" + n.name + "</option>");
                            html += "<tr class='autoappend'>";
                            html += "    <td style='width: 20%;text-align: center'>";
                            html += "        " + n.name;
                            html += "    </td>";
                            html += "    <td style='width: 15%;text-align: center'>";
                            html += "        " + n.type;
                            html += "    </td>";
                            html += "    <td style='width: 15%;text-align: center'>";
                            html += "        <input class='zhu_cb' type='checkbox' index='" + i + "' name='cb_" + n.name + "' column='t." + n.name + "'>";
                            html += "    </td>";
                            html += "    <td style='width: 35%;text-align: center'>";
                            html += "       <select id='select_condition_" + n.name + "' class='zhu_condition' index='" + i + "'  column='t." + n.name + "'>";
                            html += "           <option value='0'></option>";
                            html += "           <option value='1'>不等于</option>";
                            html += "           <option value='2'>小于等于</option>";
                            html += "           <option value='3'>大于等于</option>";
                            html += "           <option value='4'>小于</option>";
                            html += "           <option value='5'>大于</option>";
                            html += "           <option value='6'>等于</option>";
                            html += "           <option value='7'>IN</option>";
                            html += "           <option value='8'>LIKE</option>";
                            html += "       </select>";
                            html += "    <input type='text' class='zhu_condition_text' id='zhu_condition_text_" + i + "' index='" + i + "' style='width:50px' columnOri='" + n.name + "' column='t." + n.name + "' />";
                            html += "    </td>";
                            html += "    <td style='width: 15%;text-align: center'>";
                            html += "       <select id='select_" + n.name + "' class='zhu_order' index='" + i + "'  column='t." + n.name + "'>";
                            html += "           <option value='0'></option>";
                            html += "           <option value='1'>升序</option>";
                            html += "           <option value='2'>倒序</option>";
                            html += "        </select>";
                            html += "    </td>";
                            html += "</tr>";
                            zhuTableDisplayCoulumnArray[i] = "";
                        });
                        $("#TBZhu").append(html);

                        //给CheckBox赋与事件
                        $(".zhu_cb").each(function () {
                            $(this).click(function () {
                                var cb = $(this);
                                var index = parseInt(cb.attr("index"));
                                if (cb.attr("checked") == 'checked') {
                                    zhuTableDisplayCoulumnArray[index] = cb.attr("column");
                                } else {
                                    zhuTableDisplayCoulumnArray[index] = "";
                                }
                            });
                        });
                        //给条件下拉框赋与事件
                        $(".zhu_condition").each(function () {
                            $(this).change(function () {
                                var select = $(this);
                                var index = parseInt(select.attr("index"));
                                //未选择
                                if (select.val() == "0") {
                                    zhuTableConditionByArray[index] = "";
                                    //不等于
                                } else if (select.val() == "1") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " <> " + $("#zhu_condition_text_" + index.toString()).val();
                                }
                                //小于等于
                                else if (select.val() == "2") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " <= " + $("#zhu_condition_text_" + index.toString()).val();
                                }
                                //大于等于
                                else if (select.val() == "3") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " >= " + $("#zhu_condition_text_" + index.toString()).val();
                                } //小于
                                else if (select.val() == "4") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " < " + $("#zhu_condition_text_" + index.toString()).val();
                                } //大于
                                else if (select.val() == "5") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " > " + $("#zhu_condition_text_" + index.toString()).val();
                                } //等于
                                else if (select.val() == "6") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " = " + $("#zhu_condition_text_" + index.toString()).val();
                                } //IN
                                else if (select.val() == "7") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " IN " + $("#zhu_condition_text_" + index.toString()).val();
                                } //LIKE
                                else if (select.val() == "8") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " LIKE " + $("#zhu_condition_text_" + index.toString()).val();
                                }
                            });
                        });
                        //给排序下拉框赋与事件
                        $(".zhu_order").each(function () {
                            $(this).change(function () {
                                var select = $(this);
                                var index = parseInt(select.attr("index"));
                                //未选择
                                if (select.val() == "0") {
                                    zhuTableOrderByArray[index] = "";
                                    //升序
                                } else if (select.val() == "1") {
                                    zhuTableOrderByArray[index] = select.attr("column") + " ASC";
                                }
                                //降序
                                else if (select.val() == "2") {
                                    zhuTableOrderByArray[index] = select.attr("column") + " DESC";
                                }
                            });
                        });

                        //给条件文本框赋与事件
                        $(".zhu_condition_text").each(function () {
                            $(this).blur(function () {
                                var text = $(this);
                                var index = parseInt(text.attr("index"));
                                var column = text.attr("column");
                                var columnOri = text.attr("columnOri");
                                //获取下拉框选中值
                                var select = $("#select_condition_" + columnOri);
                                //未选择
                                if (select.val() == "0") {
                                    zhuTableConditionByArray[index] = "";
                                    //不等于
                                } else if (select.val() == "1") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " <> " + $("#zhu_condition_text_" + index.toString()).val();
                                }
                                //小于等于
                                else if (select.val() == "2") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " <= " + $("#zhu_condition_text_" + index.toString()).val();
                                }
                                //大于等于
                                else if (select.val() == "3") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " >= " + $("#zhu_condition_text_" + index.toString()).val();
                                } //小于
                                else if (select.val() == "4") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " < " + $("#zhu_condition_text_" + index.toString()).val();
                                } //大于
                                else if (select.val() == "5") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " > " + $("#zhu_condition_text_" + index.toString()).val();
                                } //等于
                                else if (select.val() == "6") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " = " + $("#zhu_condition_text_" + index.toString()).val();
                                } //IN
                                else if (select.val() == "7") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " IN " + $("#zhu_condition_text_" + index.toString()).val();
                                } //LIKE
                                else if (select.val() == "8") {
                                    zhuTableConditionByArray[index] = select.attr("column") + " LIKE " + $("#zhu_condition_text_" + index.toString()).val();
                                }
                            });
                        });
                    }
                });
            });

            //从表下拉框
            $("#<%=ddlTable2.ClientID %>").change(function () {
                congTableName = $(this).val();
                //清空显示字段集合
                congTableDisplayColumn = "";

                $.ajax({
                    type: "get", //使用get方法访问后台
                    dataType: "json", //返回json格式的数据
                    url: "/Admin/LabelManage/GetTableColumn.aspx", //要访问的后台地址
                    data: "table=" + $(this).val(), //要发送的数据
                    success: function (msg) { //msg为返回的数据，在这里做数据绑定
                        //联接字段-从表 添加字段
                        $("#CongTableColumn").html("<option>请选择</option>");
                        var data = msg.Table;
                        var html = "";
                        $.each(data, function (i, n) {
                            //联接字段-从表 添加字段
                            $("#CongTableColumn").append("<option>" + n.name + "</option>");
                            html += "<tr class='autoappend'>";
                            html += "    <td style='width: 20%;text-align: center'>";
                            html += "        " + n.name;
                            html += "    </td>";
                            html += "    <td style='width: 15%;text-align: center'>";
                            html += "        " + n.type;
                            html += "    </td>";
                            html += "    <td style='width: 15%;text-align: center'>";
                            html += "        <input class='cong_cb' type='checkbox' index='" + i + "' name='cb_" + n.name + "' column='t1." + n.name + "'>";
                            html += "    </td>";
                            html += "    <td style='width: 35%;text-align: center'>";
                            html += "       <select id='cong_select_condition_" + n.name + "' class='cong_condition' index='" + i + "'  column='t1." + n.name + "'>";
                            html += "           <option value='0'></option>";
                            html += "           <option value='1'>不等于</option>";
                            html += "           <option value='2'>小于等于</option>";
                            html += "           <option value='3'>大于等于</option>";
                            html += "           <option value='4'>小于</option>";
                            html += "           <option value='5'>大于</option>";
                            html += "           <option value='6'>等于</option>";
                            html += "           <option value='7'>IN</option>";
                            html += "           <option value='8'>LIKE</option>";
                            html += "       </select>";
                            html += "    <input type='text' class='cong_condition_text' id='cong_condition_text_" + i + "' index='" + i + "' style='width:50px' columnOri='" + n.name + "' column='t1." + n.name + "' />";
                            html += "    </td>";
                            html += "    <td style='width: 15%;text-align: center'>";
                            html += "       <select id='select_" + n.name + "' class='cong_order' index='" + i + "'  column='t1." + n.name + "'>";
                            html += "           <option value='0'></option>";
                            html += "           <option value='1'>升序</option>";
                            html += "           <option value='2'>倒序</option>";
                            html += "        </select>";
                            html += "    </td>";
                            html += "</tr>";
                            congTableDisplayCoulumnArray[i] = "";
                        });
                        $("#TBCong").append(html);

                        //给CheckBox赋与事件
                        $(".cong_cb").each(function () {
                            $(this).click(function () {
                                var cb = $(this);
                                var index = parseInt(cb.attr("index"));
                                if (cb.attr("checked") == 'checked') {
                                    congTableDisplayCoulumnArray[index] = cb.attr("column");
                                } else {
                                    congTableDisplayCoulumnArray[index] = "";
                                }
                            });
                        });
                        //给条件下拉框赋与事件
                        $(".cong_condition").each(function () {
                            $(this).change(function () {
                                var select = $(this);
                                var index = parseInt(select.attr("index"));
                                //未选择
                                if (select.val() == "0") {
                                    congTableConditionByArray[index] = "";
                                    //不等于
                                } else if (select.val() == "1") {
                                    congTableConditionByArray[index] = select.attr("column") + " <> " + $("#cong_condition_text_" + index.toString()).val();
                                }
                                //小于等于
                                else if (select.val() == "2") {
                                    congTableConditionByArray[index] = select.attr("column") + " <= " + $("#cong_condition_text_" + index.toString()).val();
                                }
                                //大于等于
                                else if (select.val() == "3") {
                                    congTableConditionByArray[index] = select.attr("column") + " >= " + $("#cong_condition_text_" + index.toString()).val();
                                } //小于
                                else if (select.val() == "4") {
                                    congTableConditionByArray[index] = select.attr("column") + " < " + $("#cong_condition_text_" + index.toString()).val();
                                } //大于
                                else if (select.val() == "5") {
                                    congTableConditionByArray[index] = select.attr("column") + " > " + $("#cong_condition_text_" + index.toString()).val();
                                } //等于
                                else if (select.val() == "6") {
                                    congTableConditionByArray[index] = select.attr("column") + " = " + $("#cong_condition_text_" + index.toString()).val();
                                } //IN
                                else if (select.val() == "7") {
                                    congTableConditionByArray[index] = select.attr("column") + " IN " + $("#cong_condition_text_" + index.toString()).val();
                                } //LIKE
                                else if (select.val() == "8") {
                                    congTableConditionByArray[index] = select.attr("column") + " LIKE " + $("#cong_condition_text_" + index.toString()).val();
                                }
                            });
                        });
                        //给排序下拉框赋与事件
                        $(".cong_order").each(function () {
                            $(this).change(function () {
                                var select = $(this);
                                var index = parseInt(select.attr("index"));
                                //未选择
                                if (select.val() == "0") {
                                    congTableOrderByArray[index] = "";
                                    //升序
                                } else if (select.val() == "1") {
                                    congTableOrderByArray[index] = select.attr("column") + " ASC";
                                }
                                //降序
                                else if (select.val() == "2") {
                                    congTableOrderByArray[index] = select.attr("column") + " DESC";
                                }
                            });
                        });
                    }
                });

            });

        });


        function LoadColumn() {
            var tips = "请选择";
            //判断是否选择了主表
            if ($("#ddlTable2").val() == tips) {
                alert("请选择主表");
                return;
            }

            zhuTableDisplayColumn = "";
            for (var i = 0; i < zhuTableDisplayCoulumnArray.length; i++) {
                if (zhuTableDisplayCoulumnArray[i] != "") {
                    if (zhuTableDisplayColumn == "") {
                        zhuTableDisplayColumn += zhuTableDisplayCoulumnArray[i];
                    }
                    else {
                        zhuTableDisplayColumn += "," + zhuTableDisplayCoulumnArray[i];
                    }
                }
            }
            congTableDisplayColumn = "";
            for (var i = 0; i < congTableDisplayCoulumnArray.length; i++) {
                if (congTableDisplayCoulumnArray[i] != "") {
                    if (zhuTableDisplayCoulumnArray == "") {
                        congTableDisplayColumn += congTableDisplayCoulumnArray[i] + " as t1_" + congTableDisplayCoulumnArray[i].replace("t1.","");
                    }
                    else {
                        congTableDisplayColumn += "," + congTableDisplayCoulumnArray[i] + " as t1_" + congTableDisplayCoulumnArray[i].replace("t1.", "");
                    }
                }
            }

             orderBy = "";
            //判断排序
            for (var i = 0; i < zhuTableOrderByArray.length; i++) {
                if (zhuTableOrderByArray[i] != "") {
                    if (orderBy == "") {
                        orderBy += " ORDER BY " + zhuTableOrderByArray[i];
                    }
                    else {
                        orderBy += "," + zhuTableOrderByArray[i];
                    }
                }
            }
            for (var i = 0; i < congTableOrderByArray.length; i++) {
                if (congTableOrderByArray[i] != "") {
                    if (orderBy == "") {
                        orderBy += " ORDER BY " + congTableOrderByArray[i];
                    }
                    else {
                        orderBy += "," + congTableOrderByArray[i];
                    }
                }
            }
            //判断TOP
            var top = "";
            if ($("#<%=Count.ClientID %>").val() != "") {
                top = " TOP " + $("#<%=Count.ClientID %>").val() + " ";
            }
            //判断条件
            var condition = "";
            for (var i = 0; i < zhuTableConditionByArray.length; i++) {
                //过滤错误数据
                if (typeof (zhuTableConditionByArray[i]) == "undefined") {
                    continue;
                }
                if (zhuTableConditionByArray[i] != "") {
                    if (condition == "") {
                        condition = " WHERE " + zhuTableConditionByArray[i];
                    } else {
                        condition += " AND " + zhuTableConditionByArray[i];
                    }
                }
            }
            for (var i = 0; i < congTableConditionByArray.length; i++) {
                //过滤错误数据
                if (typeof (congTableConditionByArray[i]) == "undefined") {
                    continue;
                }
                if (congTableConditionByArray[i] != "") {
                    if (condition == "") {
                        condition = " WHERE " + congTableConditionByArray[i];
                    } else {
                        condition += " AND " + congTableConditionByArray[i];
                    }
                }
            }


            //联接SQL
            var join = "";
            //判断是否有从表
            if ($("#<%=ddlTable2.ClientID %>").val() != tips) {
                if ($("#ZhuTableColumn").val() == tips) {
                    alert("请选择联接字段：主表字段");
                    return;
                }
                if ($("#CongTableColumn").val() == tips) {
                    alert("请选择联接字段：从表字段");
                    return;
                }
                join = " LEFT JOIN " + $("#<%=ddlTable2.ClientID %>").val() + " t1 ON t." + $("#ZhuTableColumn").val() + " = t1." + $("#CongTableColumn").val();
            }



            var selectSql = "SELECT " + top + zhuTableDisplayColumn + congTableDisplayColumn + " FROM " + zhuTableName + " t" + join + condition + orderBy;
            $("#<%=txtSql.ClientID %>").val(selectSql);
        }
        
        //执行SQL
        function ShowExecuteSql() {
            
        }
    </script>
</asp:Content>
